<?php
    include_once '../../../../controlador/php/conexion/Conexion10.php';
    $con = new Conexion10();
    
    $ingre = $_REQUEST['ingre'];
    
    $sql = "SELECT SUBSTRING(CONVERT (VARCHAR(10),ADNINGRESO.AINFECING,103),1,10)+' '+CONVERT(VARCHAR(5), ADNINGRESO.AINFECING, 114) AS FECHAINGRESO,GDENOMBRE AS ASEGURADORA,
                RTRIM(HCINGRE.DIAGINGRE) DIAGINGRE ,RTRIM(HCINGRE.DIAGNOMINGRE) DIAGNOMINGRE, RTRIM(HCEGRESO.DIAGEGRE) DIAGEGRE,RTRIM(HCEGRESO.DIAGNOMEGRE) DIAGNOMEGRE
                FROM  
                ".$con->getReal()."..GENPACIEN 
                INNER JOIN ".$con->getReal()."..ADNINGRESO ON GENPACIEN.OID=ADNINGRESO.GENPACIEN
                INNER JOIN ".$con->getReal()."..GENDETCON ON ADNINGRESO.GENDETCON=GENDETCON.OID
                OUTER APPLY (SELECT TOP 1 GENDIAGNO.DIACODIGO AS DIAGINGRE, GENDIAGNO.DIANOMBRE AS DIAGNOMINGRE 
                                        FROM ".$con->getReal()."..HCNFOLIO INNER JOIN ".$con->getReal()."..HCNDIAPAC ON HCNDIAPAC.HCNFOLIO = HCNFOLIO.OID
                                        INNER JOIN 	".$con->getReal()."..GENDIAGNO ON HCNDIAPAC.GENDIAGNO=GENDIAGNO.OID 
                                        WHERE HCNFOLIO.ADNINGRESO=ADNINGRESO.OID AND HCNDIAPAC.HCPDIAPRIN=1 AND HCNDIAPAC.HCPDIAPING=1) AS HCINGRE
                OUTER APPLY (SELECT GENDIAGNO.DIACODIGO AS DIAGEGRE, GENDIAGNO.DIANOMBRE AS DIAGNOMEGRE 
                                        FROM ".$con->getReal()."..HCNFOLIO INNER JOIN ".$con->getReal()."..HCNDIAPAC ON HCNDIAPAC.HCNFOLIO = HCNFOLIO.OID
                                        INNER JOIN 	".$con->getReal()."..GENDIAGNO ON HCNDIAPAC.GENDIAGNO=GENDIAGNO.OID 
                                        WHERE HCNFOLIO.ADNINGRESO=ADNINGRESO.OID AND HCNDIAPAC.HCPDIAPRIN=1 AND HCNDIAPAC.HCPDIAEGR=1) AS HCEGRESO
                WHERE 
                 ADNINGRESO.AINCONSEC='".$ingre."' AND  ADNINGRESO.AINCAUING=9
                 AND ADNINGRESO.AINESTADO NOT IN (2)
                GROUP BY SUBSTRING(CONVERT (VARCHAR(10),ADNINGRESO.AINFECING,103),1,10)+' '+CONVERT(VARCHAR(5), ADNINGRESO.AINFECING, 114),GDENOMBRE,
                HCINGRE.DIAGINGRE ,HCINGRE.DIAGNOMINGRE, HCEGRESO.DIAGEGRE,HCEGRESO.DIAGNOMEGRE";
    $res = $con->query($sql) or die("error");
    while($re = mssql_fetch_array($res)){
        $lista[] = $re;
    }
    echo json_encode($lista);
    $con->cerrarConexion();
?>
